package dao.impl;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import dao.AdminDao;
import model.Book;
import model.BookCheck;
import model.BorrowBook;
import model.Reader;
import util.JDBCUtiles;

public class AdminDaoImpl implements AdminDao {

	@Override
	public Reader getReaderByIdAndPwd(String id, String pwd) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql = "SELECT * FROM reader WHERE ReaderID = ? AND ReaderPwd = ? AND ReaderType='2'" ;
		return qr.query(sql, new BeanHandler<Reader>(Reader.class),id,pwd);
	}

	@Override
	public List queryUserList() throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql = "SELECT ReaderID,ReaderName,ReaderDept,ReaderPhone FROM reader WHERE ReaderType='1'" ;
		return qr.query(sql, new BeanListHandler<Reader>(Reader.class));
	}

	@Override
	public boolean delUserById(String id) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql  = "DELETE FROM reader WHERE ReaderID=?";	
		int i = qr.update(sql,id);
		return  i > 0 ? true : false;
	}

	@Override
	public boolean addAdminById(String id) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql  = "UPDATE reader SET ReaderType='2' WHERE ReaderID=?";	
		int i = qr.update(sql,id);
		return  i > 0 ? true : false;
	}

	@Override
	public boolean addBook(Book bk) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql  = "INSERT INTO book VALUES(?,?,?,?,?,?,?)";	
		int i = qr.update(sql,bk.getISBN(),bk.getTypeName(),bk.getBookName(),bk.getAuthor(),bk.getPublish(),bk.getUnitPrice(),bk.getCount());
		return  i > 0 ? true : false;
	}

	@Override
	public List queryCheckBookList() throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql = "SELECT borrowbook.ISBN,book.BookName,borrowbook.ReaderID,reader.ReaderName,borrowbook.BorrowDate,borrowbook.ReturnDate,book.Count,borrowbook.State "
				+ "FROM book,borrowbook,reader "
				+ "WHERE book.ISBN=borrowbook.ISBN AND reader.ReaderID=borrowbook.ReaderID AND borrowbook.State='待审核'" ;
		return qr.query(sql, new BeanListHandler<BookCheck>(BookCheck.class));
	}

	@Override
	public boolean borrowBook(String bid , String uid) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql  = "UPDATE borrowbook SET State='通过' WHERE ISBN=? AND ReaderID=?";	
		int i = qr.update(sql,bid,uid);
		return  i > 0 ? true : false;
	}

	@Override
	public boolean minusBook(String bid) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql  = "UPDATE book SET Count=Count-1 WHERE ISBN=?";	
		int i = qr.update(sql,bid);
		return  i > 0 ? true : false;
	}

	@Override
	public boolean addBook(String bid) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql  = "UPDATE book SET Count=Count+1 WHERE ISBN=?";	
		int i = qr.update(sql,bid);
		return  i > 0 ? true : false;
	}

	@Override
	public boolean disBorrowBook(String bid, String uid) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql  = "UPDATE borrowbook SET State='未通过' WHERE ISBN=? AND ReaderID=?";	
		int i = qr.update(sql,bid,uid);
		return  i > 0 ? true : false;
	}

	@Override
	public List queryReturnBookList() throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql = "SELECT bookback.ISBN,book.BookName,bookback.ReaderID,reader.ReaderName,bookback.ReturnDate,bookback.State "
				+ "FROM reader,bookback,book "
				+ "WHERE reader.ReaderID=bookback.ReaderID AND bookback.ISBN=book.ISBN AND bookback.State='待审核'" ;
		return qr.query(sql, new BeanListHandler<BookCheck>(BookCheck.class));
	}

	@Override
	public boolean passReturn(String bid, String uid) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql  = "UPDATE bookback SET State='已还' WHERE ReaderID=? AND ISBN=?";	
		int i = qr.update(sql,uid,bid);
		return  i > 0 ? true : false;
	}

	@Override
	public boolean delBorrow(String bid, String uid) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql  = "DELETE FROM borrowbook WHERE ReaderID=? AND ISBN=?";	
		int i = qr.update(sql,uid,bid);
		return  i > 0 ? true : false;
	}

}
